﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using CNative;
using CNative.Dapper.Utils;
//using CNative.DbUtils;
using CNative.Utilities;
using Dapper;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace UnitTest461
{
    [TestClass]
    public class UnitTest461DbUtils
    {
        private ISqlBuilder DRY2 = null;
        private ISqlBuilder sqlBuilder = null;
private ISqlBuilder sqlBuilderp = null;
       // private ISqlBuilder<TClass> sqlBuilder1 = null;
        //private CNative.DataRepository.IRepository drMysql = null;
        // private CNative.DataRepository.IRepository drsqLite = null;
        //在运行每个测试之前，使用 TestInitialize 来运行代码
        [TestInitialize()]
        public void MyTestInitialize()
        {
            //DRY2 = new  SqlBuilder("BaseDb");
            sqlBuilder = new SqlBuilder("BaseDb");

            //sqlBuilderp = new SqlBuilder("PostgreSql");

            //drMysql = new CNative.DbUtils.DbRepository("MySql");
            //drsqLite = new CNative.DbUtils.DbRepository("Sqlite");

        }
        [TestMethod]
        public void TestGetIQueryable()
        {
            var ss3 = String.Format("{0:0.##}", 123.4567);      // "123.46"
            var ss23 = String.Format("{0:0.##}", 123.4);         // "123.4"
            var ss233 = String.Format("{0:0.##}", 123.0);         // "123"

            var ent = new Entity_persons()
            { id = 100000001, adress = "Classify", name = "name", remark = "1,2,3" };
            var dt = DateTime.Now;
            var ss = "ss";
            var dd = new List<int>() { 2, 3, 45, 6 };
            var sql3 = DRY2.DoSelect.Where<Entity_persons>(g => g.name.In("1"));

            var sqlent = DRY2.CreateSqlEntity();
            var whe = ExpressionHelper.BuildWhere<Entity_persons>(sqlent,// t =>!("111" == t.name || t.id < 200+1)
                g => g.id.Between(10, 20) && g.id.In(dd) && (ss == "" || g.name == ss || g.id == 2) && g.adress == "dddd" && g.createTime == DateTime.Now && g.id < 200 + 1
                , true);

            var sql = DRY2.DoSelect.Where<Entity_persons>(g => g.id == 2 + 1 && (ss == "" || g.name == ss || g.id == 2) && g.adress == "dddd" && g.adress == ent.adress && g.id < 200 + 1);
            var sssds = DRY2.DoSelect.Query<Entity_persons>();
            var ssss = DRY2.DoSelect.Query<Entity_persons>();
            var sss = DRY2.DoSelect.Where<Entity_persons>(g => g.id < 20)
             .OrderBy<Entity_persons>(aa => aa.id)
             .OrderByDescending<Entity_persons>(aa => aa.name); 

            var entpersons = new Entity_persons() { id = -3, name = "xjhsss", updateTime = DateTime.Now };
            DRY2.DoDelete.Delete(entpersons);
            DRY2.DoInsert.Insert(entpersons);
            DRY2.DoUpdate.Update(entpersons);
            DRY2.DoDelete.Delete(entpersons);
            DRY2.DoUpdate.Set<Entity_persons>(f => new Entity_persons { adress = "fadsfas" })
                .Where<Entity_persons>(f => f.id == 2);

            //var entpersons = new Entity_persons() { id = 3, name = "xjhsss" };
            //drMysql.Insert(entpersons);
            //drMysql.Update(entpersons);
            //drMysql.Delete(entpersons);
            //drMysql.UpdateWhere<Entity_persons>((es) =>
            //{
            //    es.name = "ffffss";
            //}, f => f.id == 2);
            //var tb = drMysql.QueryDataTable(new CNative.DbUtils.SqlEntity(drMysql.DBType) { Sql = "select * from `persons` " });
            //var myss = drMysql.GetList<Entity_persons>(null, null, null, 20);


            //var sssf = drsqLite.GetList<Entity_persons>(null, null, a => a.OrderBy(o => o.Id), 20);
            //if (sssf?.Count > 0)
            //{
            //    sssf[1].test = 333.32555m;
            //    drsqLite.Save(sssf[1]);
            //    drsqLite.Delete(sssf[0]);
            //    drsqLite.Execute(drsqLite.GetMergeSql(sssf[0]));
            //}
            var ssd = DateTime.MinValue;


        }

        [TestMethod]
        public void TestInsertFrom()
        {
            var id = System.Guid.NewGuid().GetHashCode();
            var ent = new Entity_persons()
            { id = 1000000021, adress = "Classify", name = "name", remark = "1,2,3" };
            DRY2.DoDelete.Delete(ent);
            var ss = DRY2.DoInsert.Insert(ent);
            var guid = Guid.NewGuid();
            int[] arr = new int[] { 5, 8, 6 };
            string[] arr2 = new string[] { "1", "2", "3" };
            var sssff = new List<int>(arr);

            //LambdaToSqlHelper.GetInsertField<Entity_persons>(f => f.id == 2, f => f.name == "xjh");
            //return;
            //DRY2.InsertFrom<Entity_persons>("[dbo].[test]", w => 1 == 1, new Dictionary<string, object>()
            //{
            //    { "Remark", "testsssxjh" },{ "GuidId", Guid.NewGuid() }
            //});

            var sql = DRY2.DoSelect.Where<Entity_persons>(g =>
                1 == 3 || g.createTime == DateTime.Now || g.id == 4 && g.id.In(sssff) &&
                g.name.In(arr2) && (g.adress.Like("Clas%sify") || g.name.Equals("nam(e16'91780562")));

            // DRY2.InsertFrom<Entity_配置信息表>("[FGCPOE].[dbo].[配置信息表2]", w => w.Id.In(3,5,6), f=> 1 == 1 && f.Remark== "testsssxjh333"&& 1==2 && f.GuidId== Guid.NewGuid());
        }

        private static string ConvertToSetCommand<T>(Expression<Func<T, T>> exp)
        {
            if (exp.Body.NodeType != ExpressionType.MemberInit)
            {
                throw new ArgumentException("The expression must have an object initializer.", "exp");
            }

            var builder = new StringBuilder("SET ", 100);
            exp = (Expression<Func<T, T>>)new OmitParametersVisitor().Visit(exp);

            var memberInit = (MemberInitExpression)exp.Body;
            foreach (var assignment in memberInit.Bindings)
            {
                builder.Append(assignment.ToString());
                builder.Append(", ");
            }
            builder.Length -= 2; // Remove the last comma

            return builder.ToString();
        }

        private class OmitParametersVisitor : ExpressionVisitor
        {
            protected override Expression VisitMember(MemberExpression node)
            {
                if (node.Expression != null && node.Expression.NodeType == ExpressionType.Parameter)
                {
                    return Expression.Parameter(node.Type, node.Member.Name);
                }
                else
                {
                    return base.VisitMember(node);
                }
            }
        }

        [TestMethod]
        public void Testlog()
        {
            var id = System.Guid.NewGuid().GetHashCode();
            CNative.Utilities.NlogHelper.Error("Error:" + id);
            var ent = new Entity_persons()
            { id = 1000000021, adress = "Classify", name = "name", remark = "1,2,3" };
            CNative.Utilities.NlogHelper.Debug("Debug:" + ent.ToJson());
            CNative.Utilities.NlogHelper.Info("Info:" + DateTime.Now);
            CNative.Utilities.NlogHelper.Fatal("Fatal:" + ent.ToJson());
            CNative.Utilities.NlogHelper.Warn("Warn:" + ent.ToJson());
        }
        [TestMethod]
        public void Testlog4()
        {
            var id = System.Guid.NewGuid().GetHashCode();
            CNative.Utilities.Log4NetHelper.Error("Error:" + id);
            var ent = new Entity_persons()
            { id = 1000000021, adress = "Classify", name = "name", remark = "1,2,3" };
            CNative.Utilities.Log4NetHelper.Debug("Debug:" + ent.ToJson());
            CNative.Utilities.Log4NetHelper.Info("Info:" + DateTime.Now);
            CNative.Utilities.Log4NetHelper.Fatal("Fatal:" + ent.ToJson());
            CNative.Utilities.Log4NetHelper.Warn("Warn:" + ent.ToJson());
        }

        [TestMethod]
        public void TestConvertType()
        {
            var obj = FuncTable2Entity.ConvertType("322", typeof(int));
            var obj3 = FuncTable2Entity.ConvertType("322.55", typeof(int));
            var obj4 = FuncTable2Entity.ConvertType("322.55", typeof(decimal));
            var obj43 = FuncTable2Entity.ConvertType("322.55", typeof(double));
            var obj34 = FuncTable2Entity.ConvertType("16", typeof(System.Reflection.BindingFlags));
        }

        [TestMethod]
        public void TestFastReflection()
        {
            var ent = new Entity_persons()
            { id = 1000000021, adress = "Classify", name = "name", remark = "1,2,3" };
            //System.IO.Directory.CreateDirectory(@"test\test2");
            NlogHelper.Info("FastInvoke：" + ent.FastInvoke("getPublic"));
            NlogHelper.Info("FastInvoke：" + ent.FastInvoke("getInternal"));
            NlogHelper.Info("FastInvoke：" + ent.FastInvoke("getprotected"));
            NlogHelper.Info("FastInvoke：" + ent.FastInvoke("getPrivate", new object[] { null, "1" }));

            var ent2 = "Entity_persons2".FastInstance("xjh");
            NlogHelper.Info("FastInvoke by type：" + ent2.FastInvoke("getPublic"));
            NlogHelper.Info("FastInvoke by type：" + ent2.FastInvoke("getInternal"));
            NlogHelper.Info("FastInvoke by type：" + ent2.FastInvoke("getprotected"));
            NlogHelper.Info("FastInvoke by type：" + ent2.FastInvoke("getPrivate", new object[] { null, "1" }));
        }

        //单表操作测试
        [TestMethod]
        public void TestSelectSqlBuilder1()
        {
            var lst = sqlBuilder.doSelect<Entity_persons>()//查询集合+排序+TOP
                 .Fields(s => new { s.id, s.name, s.adress })//添加查询多个字段
                 .Top(8)
                 // .Where(w => w.id.Between(10, 20))
                 .OrderByDescending(d => d.createTime)//按列倒向排序
                 .Query(); //返回结果

            var lst2 = sqlBuilder.doSelect<Entity_persons>()//取单列+倒序
                 .Fields(s => s.name.SQL_UCASE())// 添加查询单个字段带别名
                 // .Where(w => w.id.Between(10, 20))
                .OrderByDescending(d => d.updateTime) //按列倒向排序
                 .GetSingle<string>(); //返回结果


            var lst3 = sqlBuilder.doSelect<Entity_persons>()//取单行+排序
                .Fields(s => new { s.id, s.name, s.adress })//添加查询多个字段
                .Where(w => w.id.Between(11, 20))
                .OrderBy(d => d.id) //按列排序
                .GetSingleRow(); //返回结果

            var lst4 = sqlBuilder.doSelect<Entity_persons>().Count(w => w.id.Between(11, 20)); //返回结果

        }

        [TestMethod]
        public void TestInsertSqlBuilder1()
        {
            var jg = new Entity_DMJGXXB() { OrgId = 1002, jgdm = "56783", jgmc = "方舱医院" };

            var ret = sqlBuilder.doDelete<Entity_DMJGXXB>()//实体删除
                 .Delete(jg)
                 .Exec;
            ret = sqlBuilder.doInsert<Entity_DMJGXXB>()//插入实体
                .Insert(jg)
                .Execute();

            ret = sqlBuilder.doDelete<Entity_DMJGXXB>()//表达式删除
                 .Where(s => s.OrgId == 1003)
                 .Execute();
            ret = sqlBuilder.doInsert<Entity_DMJGXXB>()//表达式插入
                .Insert(new Entity_DMJGXXB()
                {
                    OrgId = 1003,
                    jgdm = "532236783",
                    jgmc = "方舱医院3",
                    CreateId = 0,
                    UpdateId = 0,
                    CreateTime = DateTime.Now,
                    UpdateTime = DateTime.Now
                })
                .Exec;

            //表复制
            var ret1 = sqlBuilder.doDelete<Entity_DMCZYJGDYB2>()
                    .Where(s => s.ksid == 1100453)
                    .Execute();

            var ret22 = sqlBuilder.doInsert<Entity_DMCZYJGDYB2>()//表复制
                .InsertSelect<Entity_DMCZYJGDYB>(s => new Entity_DMCZYJGDYB2()
                {
                    OrgId = s.OrgId,
                    czyid = s.czyid,
                    czyjgdyid = s.czyjgdyid,
                    czyxm = s.czyxm,
                    jgmc = "fasdfafdsaf",
                    ksid = s.ksid,
                    ksmc = s.ksmc
                }, s => s.ksid == 1100453)
                .Exec;
        }
        [TestMethod]
        public void TestUpdateSqlBuilder1()
        {
            var jg = new Entity_DMJGXXB()
            {
                OrgId = 1003,
                jgdm = "5322336783",
                jgmc = "方舱医院32",
                CreateId = 0,
                UpdateId = 0,
                jgjpm = "7oipsafdasfd",
                CreateTime = DateTime.Now,
                UpdateTime = DateTime.Now
            };

            var ret = sqlBuilder.doUpdate<Entity_DMJGXXB>()
               .Set(s => new Entity_DMJGXXB()
               {
                   jgdm = "5322336783",
                   jgmc = "方舱医院232aaa",
                   CreateId = 0,
                   UpdateId = 0,
                   jgjpm = "ffff",
                   UpdateTime = DateTime.Now
               })
               .Where(s => s.OrgId == jg.OrgId)
               .Exec;

            jg.jgjpm = "345454";
            ret = sqlBuilder.DoUpdate
               .Update(jg, "jgjpm");

            var ret1 = sqlBuilder.doDelete<Entity_DMJGXXB>()
                  .Where(s => s.OrgId == 1005)
                  .Execute();

            //更新或插入
            var ret23 = sqlBuilder.DoUpdate
                .UpdateOrInsert(jg, w => w.OrgId == jg.OrgId);

            ret = sqlBuilder.doUpdate<Entity_DMJGXXB>()
                .UpdateOrInsert(s => new Entity_DMJGXXB()
                {
                    OrgId = 1006,
                    jgdm = "5322336783",
                    jgmc = "方舱医院232",
                    CreateId = 0,
                    UpdateId = 0,
                    jgjpm = "wsafdasfd",
                    CreateTime = DateTime.Now,
                    UpdateTime = DateTime.Now
                }, w => w.OrgId == 1006);

        }

        //多表操作测试
        [TestMethod]
        public void TestSelectFieldExprSqlBuilder1()
        {
            //查寻时加集合函数
            var ret1 = sqlBuilder.doSelect<Entity_DMCZYJGDYB2>()
                .Fields(f => new { czyjgdyid = f.czyjgdyid.SQL_MAX() })
                .Where(s => s.ksid == 1100453)
                .GetSingle<object>();

            var ret2 = sqlBuilder.doSelect<Entity_DMCZYJGDYB2>()
               .Fields(f => new
               { f.czyid, f.czyxm, jgmc = f.jgmc.SQL_UCASE(), ksmc = f.jgmc.SQL_SUBSTR(2, 3) })
               //.Where(s => s.ksid == 1100453)
               .Where(s => s.ksid == 1100453 && s.jgmc.SQL_UCASE() == "FASDFAFDSAF")
               .Query();

            var ret3 = sqlBuilder.doSelect<Entity_DMCZYJGDYB2>()
                .Fields(f => new { f.ksid, f.ksmc, czyjgdyid = f.czyjgdyid.SQL_MAX() })
                .Where(s => s.OrgId == 1001)
                .GroupBy(g => new { g.ksid, g.ksmc })
                .OrderBy(g => g.ksid)
                .Query();

            var ret4 = sqlBuilder.doSelect<Entity_DMCZYJGDYB2>()
                .Fields(f => new { f.ksid, f.ksmc, czyjgdyid = f.czyjgdyid.SQL_COUNT(), czyid = f.czyjgdyid.SQL_MAX() })
                .Where(s => s.OrgId == 1001)
                .GroupBy(g => new { g.ksid, g.ksmc })
                .Having(h => h.czyjgdyid.SQL_COUNT() > 3 && h.czyjgdyid.SQL_MAX() > 0)
                .OrderBy(g => g.ksid)
                .Query();
        }

        [TestMethod]
        public void TestSelectSqlBuilder()
        {
            var lst = sqlBuilder.DoSelect
                .From<Entity_persons>("a")
                 //.Fields<Entity_persons>()//添加查询多个字段
                 .Fields<Entity_persons>(s => new { s.id, s.name, adress=s.adress.SQL_NVL("无") })//添加查询多个字段
                 .Fields<Entity_persons>(s => s.adress, "a")// 添加查询单个字段带别名
                 .Top(8)
                 .Where<Entity_persons>(w => w.id.Between(10, 20))
                 .OrderByDescending<Entity_persons>(d => d.createTime)//按列倒向排序
                 .Query<Entity_persons>(); //返回结果

            var lst2 = sqlBuilder.DoSelect
                 .From<Entity_persons>()
                 .Fields("name AS ad")// 添加查询单个字段带别名
                 .Where<Entity_persons>(w => w.id.Between(10, 20))
                 .OrderByDescending<Entity_persons>(d => d.updateTime) //按列倒向排序
                 .GetSingle<string>(); //返回结果


            var lst3 = sqlBuilder.DoSelect
                .From<Entity_persons>()
                .Fields<Entity_persons>(s => new { s.id, s.name, s.adress })//添加查询多个字段
                .Where<Entity_persons>(w => w.id.Between(11, 20))
                .OrderBy<Entity_persons>(d => d.id) //按列排序
                .GetSingleRow<Entity_persons>(); //返回结果

            var lst4 = sqlBuilder.DoSelect.Count<Entity_persons>(w => w.id.Between(11, 20)); //返回结果

        }


        [TestMethod]
        public void TestSelectSqlBuilderJoin()
        {
            var lst = sqlBuilder.DoSelect
                 .From<Entity_DMKSXXB>("k")
                 .Top(8)
                 .Fields<Entity_DMKSXXB>(s => new { s.ksid, s.ksmc, s.ksdm, s.OrgId })//添加查询多个字段
                 .Fields("k.sjksid")// 添加查询单个字段带别名
                 .Fields<Entity_DMJGXXB>(s => new { s.jgjc, s.jgdm },"j")
                 .Fields("j.jgmc")
                 .InnerJoin<Entity_DMKSXXB, Entity_DMJGXXB>((k, j) => k.OrgId == j.OrgId, "j")
                 .Where<Entity_DMKSXXB>(w => w.ksid.Between(1100440, 1100450))
                 .OrderByDescending<Entity_DMKSXXB>(d => d.ksid)//按列倒向排序
                 .Query<Entity_DMKSXXB>(); //返回结果

        }


        [TestMethod]
        public void TestPssslBuilder()
        {
            try
            {
                var conn = new System.Data.SqlClient.SqlConnection("Data Source=.;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=123456");
                conn.Open();
                const string query = "select * from DM_KSXXB";
                var ss = conn.Query<Entity_DMKSXXB>(query, null);
            }
            catch (TypeLoadException ex)
            {
                var _ = ex;
            }
            catch (Exception ex)
            {
                var _ = ex;
            }
        }

        [TestMethod]
        public void TestQueryPagingList()
        {
            var pin = (sqlBuilder as SqlBuilder).Ping();
            var dt = sqlBuilder.DoSelect.GetDateNow();
            long totalNumber = 0;

            var lst = sqlBuilder.doSelect<Entity_DMKSXXB>()
                .Fields(s => new { s.ksid, s.ksmc, s.ksdm, s.OrgId })//添加查询多个字段
                .Fields("sjksid")// 添加查询单个字段带别名
                .Where(w => w.ksid.Between(1100440, 1100450))
                .OrderByDescending(d => d.ksid)//按列倒向排序
                .QueryPagingList(2,10,ref totalNumber); //返回结果

            var pae = new PagingInfo() { PageNumber=1,PageSize=13};
            var lst2 = sqlBuilder.DoSelect
                 .From<Entity_DMKSXXB>("k")
                 .Fields<Entity_DMKSXXB>(s => new { s.ksid, s.ksmc, s.ksdm, s.OrgId })//添加查询多个字段
                 .Fields("k.sjksid")// 添加查询单个字段带别名
                 .Fields<Entity_DMJGXXB>(s => new { s.jgjc, s.jgdm }, "j")
                 .Fields("j.jgmc")
                 .InnerJoin<Entity_DMKSXXB, Entity_DMJGXXB>((k, j) => k.OrgId == j.OrgId, "j")
                 .Where<Entity_DMKSXXB>(w => w.ksid.Between(1100440, 1100450))
                 .OrderByDescending<Entity_DMKSXXB>(d => d.ksid)//按列倒向排序
                 .QueryPagingList<Entity_DMKSXXB>(pae); //返回结果

            var ret3 = sqlBuilder.DoSelect
               .Fields<Entity_DMCZYJGDYB>(f => new { f.ksid, f.ksmc, czyjgdyid = f.czyjgdyid.SQL_MAX() })
               .Where<Entity_DMCZYJGDYB>(s => s.OrgId == 1001)
               .GroupBy<Entity_DMCZYJGDYB>(g => new { g.ksid, g.ksmc })
               .OrderBy<Entity_DMCZYJGDYB>(g => g.ksid)
              .QueryPagingList<Entity_DMCZYJGDYB>(pae); //返回结果
        }

        [TestMethod]
        public void TestInsertSqlBuilder()
        {
            var jg = new Entity_DMJGXXB() { OrgId = 1002, jgdm = "56783", jgmc = "方舱医院" };

            var ret = sqlBuilder.DoDelete
                 .Delete(jg)
                 .Exec;
            ret = sqlBuilder.DoInsert
                .Insert(jg)
                .Execute();

            ret = sqlBuilder.DoDelete
                 .Where<Entity_DMJGXXB>(s => s.OrgId == 1003)
                 .Execute();
            ret = sqlBuilder.DoInsert
                .Insert(new Entity_DMJGXXB()
                {
                    OrgId = 1003,
                    jgdm = "532236783",
                    jgmc = "方舱医院3",
                    CreateId = 0,
                    UpdateId = 0,
                    CreateTime = DateTime.Now,
                    UpdateTime = DateTime.Now
                })
                .Exec;


        }

        [TestMethod]
        public void TestUpdateSqlBuilder()
        {
            var jg = new Entity_DMJGXXB()
            {
                OrgId = 1003,
                jgdm = "5322336783",
                jgmc = "方舱医院32",
                CreateId = 0,
                UpdateId = 0,
                jgjpm = "7oipsafdasfd",
                CreateTime = DateTime.Now,
                UpdateTime = DateTime.Now
            };

            var ret = sqlBuilder.DoUpdate
               .Set<Entity_DMJGXXB>(s => new Entity_DMJGXXB()
               {
                   jgdm = "5322336783",
                   jgmc = "方舱医院232aaa",
                   CreateId = 0,
                   UpdateId = 0,
                   jgjpm = "ffff",
                   UpdateTime = DateTime.Now
               })
               .Where<Entity_DMJGXXB>(s => s.OrgId == jg.OrgId)
               .Exec;
            jg.jgjpm = "345454";
            ret = sqlBuilder.DoUpdate
               .Update(jg, "jgjpm");

        }
        [TestMethod]
        public void TestUpdateOrInsertSqlBuilder()
        {
          var  ret1 = sqlBuilder.DoDelete
                   .Where<Entity_DMJGXXB>(s => s.OrgId == 1005)
                   .Execute();

            var jg = new Entity_DMJGXXB()
            {
                OrgId = 1005,
                jgdm = "5322336783",
                jgmc = "方舱医院54",
                CreateId = 0,
                UpdateId = 0,
                jgjpm = "fffss",
                CreateTime = DateTime.Now,
                UpdateTime = DateTime.Now
            };

            //jg.jgjpm = "345454";
            var ret = sqlBuilder.DoUpdate
                .UpdateOrInsert(jg, w => w.OrgId == jg.OrgId);

            ret = sqlBuilder.DoUpdate
                .UpdateOrInsert<Entity_DMJGXXB>(s => new Entity_DMJGXXB()
                {
                    OrgId = 1006,
                    jgdm = "5322336783",
                    jgmc = "方舱医院232",
                    CreateId = 0,
                    UpdateId = 0,
                    jgjpm = "wsafdasfd",
                    CreateTime = DateTime.Now,
                    UpdateTime = DateTime.Now
                }, w => w.OrgId == 1006);
        }

        [TestMethod]
        public void TestInsertSelectSqlBuilder()
        {
            var dt = sqlBuilder.DoSelect.GetDateNow();

            var ret1 = sqlBuilder.DoDelete
                     .Where<Entity_DMCZYJGDYB2>(s => s.ksid == 1100453)
                     .Execute();

            var ret = sqlBuilder.DoInsert
                .InsertSelect<Entity_DMCZYJGDYB2, Entity_DMCZYJGDYB>(s => new Entity_DMCZYJGDYB2()
                {
                    OrgId = s.OrgId,
                    czyid = s.czyid,
                    czyjgdyid = s.czyjgdyid,
                    czyxm = s.czyxm,
                    jgmc = "fasdfafdsaf",
                    ksid = s.ksid,
                    ksmc = s.ksmc
                }, s => s.ksid == 1100453)
                .Exec;
        }

        [TestMethod]
        public void TestSelectFieldExprSqlBuilder()
        {
            var ret1 = sqlBuilder.DoSelect
                .Fields<Entity_DMCZYJGDYB2>(f => new { czyjgdyid = f.czyjgdyid.SQL_MAX() })
                .Where<Entity_DMCZYJGDYB2>(s => s.ksid == 1100453)
                .GetSingle<object>();

            var ret2 = sqlBuilder.DoSelect
               .Fields<Entity_DMCZYJGDYB2>(f => new
               { f.czyid, f.czyxm, jgmc = f.jgmc.SQL_UCASE(), ksmc = f.jgmc.SQL_SUBSTR(2, 3) })
               .Where<Entity_DMCZYJGDYB2>(s => s.ksid == 1100453)
               .Where<Entity_DMCZYJGDYB2>(s => s.ksid == 1100453 && s.jgmc.SQL_UCASE() == "FASDFAFDSAF")
               .Query<Entity_DMCZYJGDYB2>();

            var ret3 = sqlBuilder.DoSelect
                .Fields<Entity_DMCZYJGDYB>(f => new { f.ksid, f.ksmc, czyjgdyid = f.czyjgdyid.SQL_MAX() })
                .Where<Entity_DMCZYJGDYB>(s => s.OrgId == 1001)
                .GroupBy<Entity_DMCZYJGDYB>(g => new { g.ksid, g.ksmc })
                .OrderBy<Entity_DMCZYJGDYB>(g => g.ksid)
                .Query<Entity_DMCZYJGDYB>();

            var ret4 = sqlBuilder.DoSelect
                .From<Entity_DMCZYJGDYB>()
                .Fields<Entity_DMCZYJGDYB>(f => new { f.ksid, f.ksmc, czyjgdyid = f.czyjgdyid.SQL_COUNT(), czyid= f.czyjgdyid.SQL_MAX() })
                .Where<Entity_DMCZYJGDYB>(s => s.OrgId == 1001)
                .GroupBy<Entity_DMCZYJGDYB>(g => new { g.ksid, g.ksmc })
                .Having<Entity_DMCZYJGDYB>(h => h.czyjgdyid.SQL_COUNT()>3 && h.czyjgdyid.SQL_MAX()>0)
                .OrderBy<Entity_DMCZYJGDYB>(g => g.ksid)
                .Query<Entity_DMCZYJGDYB>();
        }

        [TestMethod]
        public void TestSqliteSqlBuilder()
        {
            var sqlBuilder = new SqlBuilder("Sqlite");
            var pin = sqlBuilder.Ping();
            var dt = sqlBuilder.DoSelect.GetDateNow();

            long totalNumber = 0;
            var lst = sqlBuilder.doSelect<Entity_sysmenu>()
                 //.Fields(null)//添加查询多个字段
                 //.Fields("sjksid")// 添加查询单个字段带别名
                 //.Where(w => w.ksid.Between(1100440, 1100450))
                 .OrderBy(d => d.Id)//按列倒向排序
                 .QueryPagingList(3, 10, ref totalNumber); //返回结果
        }

        [TestMethod]
        public void TestMySqlSqlBuilder()
        {
            var sqlBuilder = new SqlBuilder("MySql");
            var dt = sqlBuilder.DoSelect.GetDateNow();

            long totalNumber = 0;
            var lst = sqlBuilder.doSelect<Entity_sysmenu>()
                 //.Fields(null)//添加查询多个字段
                 //.Fields("name")// 添加查询单个字段带别名
                 //.Where(w => w.ksid.Between(1100440, 1100450))
                 .OrderBy(d => d.Id)//按列倒向排序
                 .QueryPagingList(3, 10, ref totalNumber); //返回结果
        }

        [TestMethod]
        public void TestPostgreSqlBuilder()
        {
            var sqlBuilder = sqlBuilderp;// new SqlBuilder("PostgreSql");
            var dt = sqlBuilder.DoSelect.GetDateNow();

            //var lst2 = sqlBuilder.doSelect<Entity_persons>()//取单列+倒序
            //     .Fields(s => s.name.SQL_UCASE())// 添加查询单个字段带别名
            //                                     // .Where(w => w.id.Between(10, 20))
            //    .OrderByDescending(d => d.updateTime) //按列倒向排序
            //     .GetSingle<string>(); //返回结果

            //long totalNumber = 0;
            //var lst = sqlBuilder.doSelect<Entity_persons>()
            //     //.Fields(null)//添加查询多个字段
            //     //.Fields("sjksid")// 添加查询单个字段带别名
            //     //.Where(w => w.ksid.Between(1100440, 1100450))
            //     .OrderBy(d => d.name)//按列倒向排序
            //     .QueryPagingList(3, 10, ref totalNumber); //返回结果
        }

        [TestMethod]
        public void TestOracleSqlBuilder()
        {
            var sqlBuilder = new SqlBuilder("Oracle");
            var dt = sqlBuilder.DoSelect.GetDateNow();

            long totalNumber = 0;
            var lst = sqlBuilder.doSelect<Entity_DRUGSTOCK>()
                 //.Fields(null)//添加查询多个字段
                 //.Fields("sjksid")// 添加查询单个字段带别名
                 .Where(w => w.STORAGE== "110003")
                 //.OrderBy(d => d.Id)//按列倒向排序
                 .QueryPagingList(1, 5, ref totalNumber); //返回结果

            //var lst23 = sqlBuilder.doSelect<Entity_persons>()//取单列+倒序
            //                                                 //.Fields(s => s.name.SQL_UCASE())// 添加查询单个字段带别名
            //                                                 // .Where(w => w.id.Between(10, 20))
            //   .OrderBy(d => d.id) //按列倒向排序
            //   .QueryPagingList(3, 10, ref totalNumber); //返回结果

            Guid ss = GetNextGuid();
        }

        public Guid GetNextGuid()
        {
            byte[] b = Guid.NewGuid().ToByteArray();
            DateTime dateTime = new DateTime(1900, 1, 1);
            DateTime now = DateTime.Now;
            TimeSpan timeSpan = new TimeSpan(now.Ticks - dateTime.Ticks);
            TimeSpan timeOfDay = now.TimeOfDay;
            byte[] bytes1 = BitConverter.GetBytes(timeSpan.Days);
            byte[] bytes2 = BitConverter.GetBytes((long)(timeOfDay.TotalMilliseconds / 3.333333));
            Array.Reverse(bytes1);
            Array.Reverse(bytes2);
            Array.Copy(bytes1, bytes1.Length - 2, b, b.Length - 6, 2);
            Array.Copy(bytes2, bytes2.Length - 4, b, b.Length - 4, 4);
            return new Guid(b);
        }
    }
}
